using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data.Common;
using System.Data;
using Yogesh.ExcelXml;
using ICSharpCode.SharpZipLib.Zip;
using System.Text;
using System.Collections;
using System.Xml;
using System.Xml.XPath;
using System.Threading;
using System.Text.RegularExpressions;

/// <summary>
/// Imports from an Excel XML spreadsheet
/// </summary>
public partial class Import : System.Web.UI.Page
{
    private Database db;

    private enum FileType {
        XML,
        Properties,
        Resx,
        Firefox,
        PO
    }

    /// <summary>
    /// Clears errors from previous submissions
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void Page_Load(object sender, EventArgs e)
    {
        if(!Page.IsPostBack) {
            if(db == null || !db.Connected) {
                db = new Database();
                db.OpenConnection();
            }
            using(Database.Command cmd = db.CreateCommand()) {
                cmd.CommandText = "SELECT id, displayname FROM " + db.TablePrefix + "applications ORDER BY displayname ASC";
                Database.DataAdapter adapter = new Database.DataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                radApplication.DataSource = ds;
                radApplication.DataValueField = "id";
                radApplication.DataTextField = "displayname";
                radApplication.DataBind();
            }
            if(radApplication.Items.Count > 0) {
                radApplication.SelectedIndex = 0;
                mltImport.ActiveViewIndex = 1;
            }
        }
    }
    
    /// <summary>
    /// Uploads the file and begins importing. If the file is a zip file, all files are extracted from it before continuing.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void btnUpload_Click(object sender, EventArgs e) {
        if(uplXlsFile.HasFile) {
            try {
                string ext = Path.GetExtension(uplXlsFile.FileName).ToLowerInvariant().Substring(1); // the extension of the uploaded file (could be zip)
                string innerExtension = string.Empty; // the file within the zip
                Dictionary<string, string> files = new Dictionary<string, string>();
                StringBuilder fileBuffer = new StringBuilder();
                using(MemoryStream ms = new MemoryStream()) {
                    if(ext == "zip") {
                        ZipInputStream zipStream = new ZipInputStream(uplXlsFile.FileContent);
                        ZipEntry entry;
                        byte[] buffer = new byte[1024];
                        while((entry = zipStream.GetNextEntry()) != null) {
                            fileBuffer.Remove(0, fileBuffer.Length);
                            if(innerExtension == string.Empty) {
                                if(radFiletype.SelectedValue == "auto") {
                                    string detectedExt = Path.GetExtension(entry.Name);
                                    if(detectedExt.Length > 1) {
                                        innerExtension = detectedExt.ToLowerInvariant().Substring(1); // use the extension of the first file in the archive
                                    }
                                } else {
                                    innerExtension = radFiletype.SelectedValue;
                                }
                            }
                            StreamReader reader = new StreamReader(zipStream);
                            files.Add(entry.Name, reader.ReadToEnd());
                        }
                    } else {
                        using(StreamReader reader = new StreamReader(uplXlsFile.FileContent)) {
                            files.Add(uplXlsFile.FileName, reader.ReadToEnd().Replace("\r", ""));
                        }
                        if(radFiletype.SelectedValue == "auto") {
                            innerExtension = Path.GetExtension(uplXlsFile.FileName).ToLowerInvariant().Substring(1);
                        } else {
                            innerExtension = radFiletype.SelectedValue;
                        }
                    }
                }
                if(innerExtension.Length == 0) {
                    throw new Exception("Could not detect file type");
                }
                if(files.Count == 0) {
                    throw new Exception("No files found");
                }
                switch(innerExtension) {
                    case "xml":
                        foreach(KeyValuePair<string, string> file in files) {
                            Session["innerExtension"] = "xml";
                            Session["importFile"] = file.Value;
                            PromptApplication(string.Format("Uploaded XML file. Click 'Import' to start."));
                        }
                        break;
                    case "properties":
                        Session["innerExtension"] = "properties";
                        Session["importFile"] = files;
                        PromptApplication(string.Format("Uploaded <strong>{0}</strong> {1}. Choose an application to which to add {2}, then click 'Import' to start.", files.Count, files.Count != 1 ? "bundles" : "bundle", files.Count == 1 ? "this bundle" : "these bundles"));
                        break;
                    case "resx":
                        Session["innerExtension"] = "resx";
                        Session["importFile"] = files;
                        PromptApplication(string.Format("Uploaded <strong>{0}</strong> {1}. Choose an application to which to add {2}, then click 'Import' to start.", files.Count, files.Count != 1 ? "bundles" : "bundle", files.Count == 1 ? "this bundle" : "these bundles"));
                        break;
                    case "po":// todo: make this parse subdirs for multiple bundles
                        Session["innerExtension"] = "po";
                        Session["importFile"] = files;
                        PromptApplication(string.Format("Uploaded <strong>{0}</strong> {1}. Choose an application to which to add {2}, then click 'Import' to start.", files.Count, files.Count != 1 ? "files" : "file", files.Count == 1 ? "this bundle" : "these bundles"));
                        break;
                    default:
                        throw new Exception("Unsupported file type - " + innerExtension);
                }
            } catch(Exception ex) {
                Label lblError = viwFiles.FindControl("lblError") as Label;
                lblError.Visible = true;
                lblError.Text = ex.Message;
            }
        } else {
            Label lblError = viwFiles.FindControl("lblError") as Label;
            lblError.Visible = true;
            lblError.Text = "No file specified";
        }
    }

    /// <summary>
    /// Prompts for the application to which to add the uploaded bundles.
    /// </summary>
    /// <param name="bundles">The uploaded bundles.</param>
    private void PromptApplication(string message){//Dictionary<string, Dictionary<string, Dictionary<string, string>>> bundles) {
        mltImport.ActiveViewIndex = 2;
        litUploadedInfo.Text = message;
    }

    /// <summary>
    /// Starts the import process.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void btnImport_Click(object sender, EventArgs e) {
        litProgress.Visible = true;
        pnlPromptApplication.Visible = false;
        if(Session["importFile"] == null || Session["importFile"] == null) {
            ListItem itm = new ListItem("Error #409: Could not load file from cache.");
            itm.Attributes.Add("class", "error");
            lstProgress.Items.Add(itm); //todo: check on error
            return;
        }
        lstProgress.Items.Clear();
        Thread worker = new Thread(new ThreadStart(StartImport));
        imgProgress.Visible = true;
        worker.Name = "ImportWorker";
        worker.IsBackground = true;
        worker.Start();
        Session["worker"] = worker;
        List<ListItem> log = new List<ListItem>();
        log.Add(new ListItem("Starting import...")); // the log is deserialized on every timer tick
        Session["log"] = log;
        Session["importDone"] = bool.FalseString;
        tmrImport.Enabled = true;
        btnImport.Enabled = false;
    }

    /// <summary>
    /// Creates a new thread and starts importing.
    /// </summary>
    private void StartImport() {
        try {
            string innerExtension = Session["innerExtension"].ToString();
            switch(innerExtension) {
                case "xml":
                    ImportXML(Session["importFile"].ToString());
                    break;
                case "properties":
                    ImportProperties(Session["importFile"] as Dictionary<string, string>);
                    break;
                case "resx":
                    ImportResx(Session["importFile"] as Dictionary<string, string>);
                    break;
                case "po":
                    ImportPO(Session["importFile"] as Dictionary<string, string>);
                    break;
            }
            LogText("Import complete.", false);
            Session["importDone"] = bool.TrueString;
        } catch(Exception ex) {
            LogText("Error restoring #406: " + ex.Message, true);
            Session["importDone"] = bool.TrueString;
        }
        Thread worker = Session["worker"] as Thread;
        if(worker != null) {
            worker.Abort();
        }
    }

    /// <summary>
    /// Imports from a ResourceBlender XML file.
    /// </summary>
    /// <param name="file">The file.</param>
    private void ImportXML(string file) {
        try {
            if(db == null || !db.Connected) {
                db = new Database();
                db.OpenConnection();
            }
            ExcelXmlWorkbook wb;
            using(MemoryStream ms = new MemoryStream(Encoding.UTF8.GetBytes(file))) {
                wb = ExcelXmlWorkbook.Import(ms);
            }
            //todo: load meta from separate sheet

            List<string> languagesThisSheet = new List<string>();
            int startingColumn = 1;
            using(Database.Command cmdCheckExisting = db.CreateCommand())
            using(Database.Command cmdInsertResource = db.CreateCommand())
            using(Database.Command cmdInsertResourceComment = db.CreateCommand())
            using(Database.Command cmdMaxRow = db.CreateCommand()) {
                cmdCheckExisting.CommandText = "SELECT COUNT(resourceid) FROM " + db.TablePrefix + "resources WHERE resourceid = @tID";
                cmdCheckExisting.AddParameterWithValue("@tID", "");
                switch(db.InstanceType) {
                    case Database.DatabaseType.MySQL:
                        cmdInsertResource.CommandText = "INSERT INTO " + db.TablePrefix + "resources (resourceid, language, translation, translatorcomment) VALUES (@tID, @lang, @trans, @comment) ON DUPLICATE KEY UPDATE translation = @trans, translatorcomment = @comment";
                        cmdInsertResourceComment.CommandText = "INSERT INTO " + db.TablePrefix + "resourcecomments (resourceid, comment) VALUES (@tID, @comm) ON DUPLICATE KEY UPDATE comment = @comm";
                        break;
                    case Database.DatabaseType.SQLServer:
                        cmdInsertResource.CommandText = "UPDATE " + db.TablePrefix + "resources SET translation = @trans, translatorcomment = @comment WHERE resourceid = @tID AND language = @lang; IF @@ROWCOUNT = 0 INSERT INTO " + db.TablePrefix + "resources (resourceid, language, translation, translatorcomment) VALUES (@tID, @lang, @trans, @comment)";
                        cmdInsertResourceComment.CommandText = "UPDATE " + db.TablePrefix + "resourcecomments SET comment = @comm WHERE resourceid = @tID; IF @@ROWCOUNT = 0 INSERT INTO " + db.TablePrefix + "resourcecomments (resourceid, comment) VALUES (@tID, @comm)";
                        break;
                }
                cmdInsertResource.AddParameterWithValue("@tID", "");
                cmdInsertResource.AddParameterWithValue("@lang", "");
                cmdInsertResource.AddParameterWithValue("@trans", "");
                cmdInsertResource.AddParameterWithValue("@comment", "");
                cmdInsertResourceComment.AddParameterWithValue("@tID", "");
                cmdInsertResourceComment.AddParameterWithValue("@comm", "");
                cmdMaxRow.CommandText = "SELECT MAX(resourceid) FROM " + db.TablePrefix + "resources";
                int maxId = 0;
                int resourcesUpdated = 0;
                int j, k;
                Regex rgxHTMLTags = new Regex("<(.|\n)*?>");
                for(int i = 0; i < wb.SheetCount; i++) {
                    LogText("Restoring from worksheet 1", false);
                    Worksheet sheet = wb[i];
                    languagesThisSheet.Clear();
                    for(int colIndex = startingColumn + 1; colIndex < sheet.ColumnCount; colIndex++) { //  first two cells are padding/labels
                        languagesThisSheet.Add(sheet[colIndex, 0].Value.ToString());
                    }
                    int numRows = sheet.RowCount,
                        rowsImported = 0,
                        logIncrements = numRows / 4;
                    for(j = 1; j < numRows; j++) {
                        try {
                            Row row = sheet[j];
                            int resourceId = 0;
                            if(!int.TryParse(row[startingColumn].Value.ToString(), out resourceId)) {
                                using(DbDataReader maxIdReader = cmdMaxRow.ExecuteReader(CommandBehavior.SingleRow)) {
                                    if(maxIdReader.HasRows) {
                                        maxIdReader.Read();
                                        int.TryParse(maxIdReader[0].ToString(), out maxId);
                                        resourceId++;
                                    }
                                    maxIdReader.Close();
                                }
                            }
                            cmdCheckExisting.Parameters["@tID"].Value = resourceId;
                            for(k = 0; k < row.CellCount - startingColumn - 1; k++) { // add each language to the resources table
                                Cell currentCell = row[k + startingColumn + 1];
                                if(k >= languagesThisSheet.Count) {
                                    LogText("Skipping extra columns at row " + j, true);
                                    continue;
                                }
                                string langAbbrev = languagesThisSheet[k];
                                string resource = currentCell.Value == null ? null : currentCell.Value.ToString();
                                if(resource != null && resource.Length > 0) {
                                    cmdInsertResource.Parameters["@tID"].Value = resourceId;
                                    cmdInsertResource.Parameters["@lang"].Value = langAbbrev;
                                    //cmdInsertResource.Parameters["@trans"].Value = resource.Replace("&gt;", ">").Replace("&lt;", "<");// HttpUtility.HtmlDecode(resource);
                                    cmdInsertResource.Parameters["@trans"].Value = rgxHTMLTags.Replace(resource, string.Empty).Replace("&gt;", ">").Replace("&lt;", "<");// HttpUtility.HtmlDecode(resource);
                                    if(!string.IsNullOrEmpty(currentCell.Comment)) {
                                        cmdInsertResource.Parameters["@comment"].Value = rgxHTMLTags.Replace(currentCell.Comment, string.Empty).Replace("&gt;", ">").Replace("&lt;", "<");
                                    } else {
                                        cmdInsertResource.Parameters["@comment"].Value = DBNull.Value;
                                    }
                                    cmdInsertResource.ExecuteNonQuery();
                                    resourcesUpdated++;
                                }
                            }
                            string comment = row[startingColumn].Comment;
                            if(comment != null && comment.Length > 0) {
                                cmdInsertResourceComment.Parameters["@tID"].Value = resourceId;
                                cmdInsertResourceComment.Parameters["@comm"].Value = rgxHTMLTags.Replace(comment, string.Empty).Replace("&gt;", ">").Replace("&lt;", "<"); ;
                                cmdInsertResourceComment.ExecuteNonQuery();
                            }
                        } catch {
                            LogText("Error reading row " + j, true);
                        }
                        // TODO: add new resource by appending rows to spreadsheet (read max id etc.)
                        rowsImported++;
                        if(rowsImported % logIncrements == 0) {
                            LogText(string.Format("Imported {0}/{1} rows", rowsImported, numRows), false);
                        }
                    }
                }
                LogText(string.Format("Added {0} elements", resourcesUpdated), false);
            }
        } catch(Exception ex) {
            LogText(ex.Message, true);
        }
    }

    /// <summary>
    /// Imports elements and resources from existing .properties bundles, then prompts for an application to which to import.
    /// </summary>
    /// <param name="files">The files.</param>
    private void ImportProperties(Dictionary<string, string> files) {
        Dictionary<string, Dictionary<string, Dictionary<string, string>>> bundles = new Dictionary<string, Dictionary<string, Dictionary<string, string>>>(); // <bundle, <elementname<language, resource>>>
        string bundleName, language, elementName, resource;
        int splitter, elementSplitter;
        Dictionary<string, Dictionary<string, string>> currentBundle;
        LogText("Parsing bundles...", false);
        foreach(KeyValuePair<string, string> file in files) { // <filename, UTF8 contents>
            string fileNameNoExt = Path.GetFileNameWithoutExtension(file.Key);
            splitter = fileNameNoExt.IndexOf('_');
            if(splitter <= 0) {
                continue;
            }
            bundleName = fileNameNoExt.Substring(0, splitter);
            language = Utils.JavaCulture2DotNetCulture(fileNameNoExt.Substring(splitter + 1));
            if(bundles.ContainsKey(bundleName)) {
                currentBundle = bundles[bundleName];
            } else {
                currentBundle = new Dictionary<string, Dictionary<string, string>>();
                bundles.Add(bundleName, currentBundle);
            }
            string[] elements = file.Value.Split(new char[] { '\n' }, StringSplitOptions.RemoveEmptyEntries);
            foreach(string totalElement in elements) {
                try {
                    elementSplitter = totalElement.IndexOf('=');
                    elementName = totalElement.Substring(0, elementSplitter);
                    resource = totalElement.Substring(elementSplitter + 1);
                    if(currentBundle.ContainsKey(elementName)) {
                        currentBundle[elementName].Add(language, resource);
                    } else {
                        Dictionary<string, string> element = new Dictionary<string, string>();
                        element.Add(language, resource);
                        currentBundle.Add(elementName, element);
                    }
                } catch(Exception ex) {
                    LogText(string.Format("Failed to parse element '{0}': {1}", totalElement, ex.Message), true);
                }
            }
        }
        ProcessBundles(bundles);
    }

    /// <summary>
    /// Imports from .NET managed resource (.resx) files.
    /// </summary>
    /// <param name="files">The files.</param>
    private void ImportResx(Dictionary<string, string> files) {
        Dictionary<string, Dictionary<string, Dictionary<string, string>>> bundles = new Dictionary<string, Dictionary<string, Dictionary<string, string>>>(); // <bundle, <elementname<language, resource>>>
        string bundleName, language, elementName, resource, dataType;
        XmlAttributeCollection attr;
        int splitter;
        Dictionary<string, Dictionary<string, string>> currentBundle;
        LogText("Parsing bundles...", false);
        foreach(KeyValuePair<string, string> file in files) { // <filename, UTF8 contents>
            string fileNameNoExt = Path.GetFileNameWithoutExtension(file.Key);
            splitter = fileNameNoExt.IndexOf('.');
            if(splitter <= 0) {
                continue;
            }
            bundleName = fileNameNoExt.Substring(0, splitter);
            language = fileNameNoExt.Substring(splitter + 1).Replace('.', '-');
            switch(language) { // the GetCultures() method lists them as this, so if they're imported as Hans and Hant, they don't match up.
                case "zh-Hans":
                    language = "zh-CHS";
                    break;
                case "zh-Hant":
                    language = "zh-CHT";
                    break;
            }
            if(bundles.ContainsKey(bundleName)) {
                currentBundle = bundles[bundleName];
            } else {
                currentBundle = new Dictionary<string, Dictionary<string, string>>();
                bundles.Add(bundleName, currentBundle);
            }
            XmlDocument doc = new XmlDocument();
            try {
                doc.LoadXml(file.Value);
            } catch(Exception ex) {
                LogText("Invalid XML file: " + file.Key, true);
                System.Diagnostics.Debug.WriteLine(ex.Message);
                continue;
            }
            if(doc.ChildNodes.Count == 0) {
                LogText("Invalid resx file: " + file.Key, true);
                continue;
            }
            XmlNodeList nodes = doc.SelectNodes("/root/data[count(value)=1]");
            foreach(XmlNode node in nodes) {
                if(node.NodeType == XmlNodeType.Element) {
                    dataType = null;
                    attr = node.Attributes;
                    if(attr["type"] != null) {
                        dataType = attr["type"].Value;
                    } else if(attr["mimetype"] != null) {
                        dataType = attr["mimetype"].Value;
                    }
                    if(attr["name"] == null) {
                        LogText(string.Format("Invalid element in file {0}: name attribute missing", file.Key), true);
                        continue;
                    } else {
                        elementName = attr["name"].Value;
                    }
                    resource = string.Empty;
                    foreach(XmlNode child in node) {
                        if(child.NodeType == XmlNodeType.Element) {
                            if(child.Name == "value") {
                                resource = child.InnerText;
                            }
                        }
                    }
                    switch(dataType) {
                        case null: // not set, resource is a string
                            if(currentBundle.ContainsKey(elementName)) {
                                currentBundle[elementName].Add(language, resource);
                            } else {
                                Dictionary<string, string> element = new Dictionary<string, string>();
                                element.Add(language, resource);
                                currentBundle.Add(elementName, element);
                            }
                            break;
                    }
                }
            }
        }
        ProcessBundles(bundles);
    }

    /// <summary>
    /// Inserts bundles, elements and translations into the database.
    /// </summary>
    /// <param name="bundles">The bundles.</param>
    private void ProcessBundles(Dictionary<string, Dictionary<string, Dictionary<string, string>>> bundles) {
        LogText("Finished parsing, starting import...", false);
        try {
            if(db == null || !db.Connected) {
                db = new Database();
                db.OpenConnection();
            }
            int numBundles = 0,
                numElements = 0,
                numResources = 0;
            using(Database.Command cmd = db.CreateCommand())
            using(Database.Command cmdInsertBundle = db.CreateCommand())
            using(Database.Command cmdCheckExisting = db.CreateCommand())
            using(Database.Command cmdInsertElement = db.CreateCommand())
            using(Database.Command cmdFindSimilar = db.CreateCommand())
            using(Database.Command cmdInsertResource = db.CreateCommand())
            using(Database.Command cmdMaxRow = db.CreateCommand()) {
                string appId = (viwApp.FindControl("radApplication") as RadioButtonList).SelectedValue,
                       defaultLanguage = string.Empty;
                string[] appLanguages = null;
                Hashtable appBundles = new Hashtable(); // <bundlename, bundleid>
                cmd.CommandText = "SELECT defaultlanguage, languages FROM " + db.TablePrefix + "applications WHERE id = @id";
                cmd.AddParameterWithValue("@id", appId);
                DbDataReader reader = cmd.ExecuteReader();
                while(reader.Read()) {
                    defaultLanguage = reader["defaultlanguage"].ToString();
                    appLanguages = reader["languages"].ToString().Trim().Split(',');
                }
                reader.Close();
                cmd.CommandText = "SELECT id, name FROM " + db.TablePrefix + "bundles WHERE application = @id";
                reader = cmd.ExecuteReader();
                while(reader.Read()) {
                    appBundles.Add(reader[1], reader[0]);
                }
                reader.Close();

                cmdInsertBundle.CommandText = "INSERT INTO " + db.TablePrefix + "bundles (id, name, application) VALUES (@id, @name, @application)";
                cmdInsertBundle.AddParameterWithValue("@id", "");
                cmdInsertBundle.AddParameterWithValue("@name", "");
                cmdInsertBundle.AddParameterWithValue("@application", appId);

                cmdCheckExisting.CommandText = "SELECT resourceid FROM " + db.TablePrefix + "elements WHERE applicationid = @appId AND bundle = @bundle AND elementname = @element";
                cmdCheckExisting.AddParameterWithValue("@appId", appId);
                cmdCheckExisting.AddParameterWithValue("@bundle", "");
                cmdCheckExisting.AddParameterWithValue("@element", "");

                cmdInsertElement.CommandText = "INSERT INTO " + db.TablePrefix + "elements (applicationid, bundle, elementname, resourceid) VALUES (@appId, @bundle, @element, @tId)";
                cmdInsertElement.AddParameterWithValue("@appId", appId);
                cmdInsertElement.AddParameterWithValue("@bundle", "");
                cmdInsertElement.AddParameterWithValue("@element", "");
                cmdInsertElement.AddParameterWithValue("@tId", "");

                cmdFindSimilar.CommandText = "SELECT resourceid FROM " + db.TablePrefix + "resources WHERE language = @language AND translation = @translation";
                cmdFindSimilar.AddParameterWithValue("@language", defaultLanguage); // Only match if the default language for this app. is present in the uploaded resources
                cmdFindSimilar.AddParameterWithValue("@translation", "");

                switch(db.InstanceType) {
                    case Database.DatabaseType.MySQL:
                        cmdInsertResource.CommandText = "INSERT INTO " + db.TablePrefix + "resources (resourceid, language, translation) VALUES (@tId, @language, @translation) ON DUPLICATE KEY UPDATE translation = @translation";
                        break;
                    case Database.DatabaseType.SQLServer:
                        cmdInsertResource.CommandText = "UPDATE " + db.TablePrefix + "resources SET translation = @translation WHERE resourceid = @tId AND language = @language; IF @@ROWCOUNT = 0 INSERT INTO " + db.TablePrefix + "resources (resourceid, language, translation) VALUES (@tId, @language, @translation)";
                        break;
                }
                cmdInsertResource.AddParameterWithValue("@tID", "");
                cmdInsertResource.AddParameterWithValue("@language", "");
                cmdInsertResource.AddParameterWithValue("@translation", "");

                cmdMaxRow.CommandText = "SELECT MAX(resourceid) FROM "+db.TablePrefix+"resources";

                int resourceId = 0;
                string bundleId;
                foreach(KeyValuePair<string, Dictionary<string, Dictionary<string, string>>> bundle in bundles) { // <bundle, <element, <language, translation>>>
                    LogText(string.Format("Importing bundle <strong>{0}</strong>", bundle.Key), false);
                    numBundles++;
                    if(appBundles.ContainsKey(bundle.Key)) {
                        bundleId = appBundles[bundle.Key].ToString();
                    } else {
                        bundleId = Utils.GenID(true);
                        cmdInsertBundle.Parameters["@id"].Value = bundleId;
                        cmdInsertBundle.Parameters["@name"].Value = bundle.Key;
                        cmdInsertBundle.ExecuteNonQuery();
                        appBundles.Add(bundle.Key, bundleId);
                    }
                    cmdCheckExisting.Parameters["@bundle"].Value = bundleId;
                    cmdInsertElement.Parameters["@bundle"].Value = bundleId;
                    foreach(KeyValuePair<string, Dictionary<string, string>> element in bundle.Value) { // <element, <language, translation>>
                        numElements++;
                        cmdCheckExisting.Parameters["@element"].Value = element.Key;
                        reader = cmdCheckExisting.ExecuteReader();
                        if(reader.HasRows) { // the element already exists, overwrite existing languages with the ones from the bundle
                            reader.Read();
                            int.TryParse(reader[0].ToString(), out resourceId);
                            reader.Close();
                        } else { // no matching elements, create one first
                            reader.Close();
                            cmdInsertElement.Parameters["@element"].Value = element.Key;

                            // check for other resources which might be have the same translation, then append the translations in this bundle to them instead of creating a duplicate resource. 
                            bool readId = false;
                            if(element.Value.ContainsKey(defaultLanguage)) {
                                var d = element.Value[defaultLanguage];
                                cmdFindSimilar.Parameters["@translation"].Value = element.Value[defaultLanguage];
                                reader = cmdFindSimilar.ExecuteReader();
                                if(reader.HasRows) { // resource of current element matches one already in the database, use the existing resource ID
                                    reader.Read();
                                    resourceId = reader.GetInt32(0);
                                    readId = true;
                                }
                                reader.Close();
                            }
                            if(!readId) { // no matching resources, find the next available resource ID
                                using(DbDataReader maxIdReader = cmdMaxRow.ExecuteReader(CommandBehavior.SingleRow)) {
                                    if(maxIdReader.HasRows) {
                                        maxIdReader.Read();
                                        int.TryParse(maxIdReader[0].ToString(), out resourceId);
                                        resourceId++;
                                    }
                                    maxIdReader.Close();
                                }
                            }
                            cmdInsertElement.Parameters["@tId"].Value = resourceId;
                            cmdInsertElement.ExecuteNonQuery();
                        }
                        cmdInsertResource.Parameters["@tId"].Value = resourceId;
                        foreach(KeyValuePair<string, string> resource in element.Value) { // <language, translation>>
                            numResources++;
                            cmdInsertResource.Parameters["@language"].Value = resource.Key;
                            cmdInsertResource.Parameters["@translation"].Value = resource.Value;
                            try {
                                cmdInsertResource.ExecuteNonQuery();
                            } catch(Exception ex) {
                                LogText(ex.Message, true);
                            }
                        }
                    }
                }
            }
            LogText(string.Format("Imported {0} resources from {1} bundles containing {2} elements.", numResources, numBundles, numElements), false);
        } catch(Exception ex) {
            LogText(ex.Message, true);
        }
    }

    private void ImportPO(Dictionary<string, string> files) {
        Dictionary<string, Dictionary<string, Dictionary<string, string>>> bundles = new Dictionary<string, Dictionary<string, Dictionary<string, string>>>(); // <BundleName, <msgId<language, translation>>>
        Dictionary<string, Dictionary<string, POMetaData>> elementData = new Dictionary<string,Dictionary<string,POMetaData>>(); // <BundleName, <ElementName, Meta-data>>
        string bundleName, language;
        int splitter;
        LogText("Parsing bundles...", false);
        if(db == null || !db.Connected) {
            db = new Database();
            db.OpenConnection();
        }
        using(Database.Command cmd = db.CreateCommand()) {
            Regex rgxUntranslatedString = new Regex("msgid \"(.*)\""),
                  rgxTranslatedString = new Regex("msgstr \"(.*)\""),
                  rgxUntranslatedPlural = new Regex("msgid_plural \"(.*)\""),
                  rgxTranslatedPlural = new Regex("msgstr\\[(\\d)\\] \"(.*)\""),
                  rgxContext = new Regex("msgctxt \".*\"");
            foreach(KeyValuePair<string, string> file in files) { // <filename, UTF8 contents>
                if(file.Value.Length == 0) {
                    continue;
                }
                string fileNameNoExt = Path.GetFileNameWithoutExtension(file.Key);
                splitter = file.Key.IndexOf('/');
                if(splitter <= 0) { // no directory means no bundle, create a new bundle
                    //todo: 1 - check this works with subdirs
                    bundleName = DateTime.Now.ToShortTimeString() + "-" + new Random().Next(100);
                } else {
                    bundleName = file.Key.Substring(0, splitter);
                }
                language = fileNameNoExt;
                if(!bundles.ContainsKey(bundleName)) {
                    bundles.Add(bundleName, new Dictionary<string, Dictionary<string, string>>());
                    elementData.Add(bundleName, new Dictionary<string, POMetaData>());
                }
                string[] entries = file.Value.Split(new string[] { "\n\n" }, StringSplitOptions.RemoveEmptyEntries);
                foreach(string entry in entries) {
                    string[] lines = entry.Split('\n');
                    try {
                        POMetaData data = new POMetaData();
                        string line;
                        StringBuilder translatorComments = null;
                        for(int i=0;i<lines.Length;i++) {
                            line = lines[i].Trim();
                            if(line.Length > 4) {
                                switch(line.Substring(0, 2)) {
                                    case "# ":
                                        if(translatorComments == null) {
                                            translatorComments = new StringBuilder();
                                        }
                                        translatorComments.AppendLine(line.Substring(2).Trim());
                                        break;
                                    case "#.":
                                        data.ExtractedComments.Add(line.Substring(2).Trim());
                                        break;
                                    case "#:":
                                        data.References.Add(line.Substring(2).Trim());
                                        break;
                                    case "#,":
                                        data.Flags.Add(line.Substring(2).Trim());
                                        break;
                                    default:
                                        if(line.Length > 7) { // min is 'msgid ""'
                                            int j;
                                            if(line.StartsWith("msgid") && line.Length >= 8) {
                                                Match matchUntranslated = rgxUntranslatedString.Match(line);
                                                if(matchUntranslated.Success && matchUntranslated.Groups[1].Value.Length == 0) { // line is msgid "" -  multiline string, move to the next line to start reading
                                                    StringBuilder multiLine = new StringBuilder();
                                                    for(j = i + 1; j < lines.Length; j++) {
                                                        string nextLine = lines[j].Trim();
                                                        if(nextLine.Length < 2 || nextLine[0] != '"') { // either an unexpected char or anything but a quote means the end of the multi-line string
                                                            break;
                                                        } else if(line.Length > 3) { // A line could contain just "", but it should be skipped if so
                                                            multiLine.Append(nextLine.Substring(1, nextLine.Length - 2));
                                                        }
                                                    }
                                                    if(multiLine.Length > 0) {
                                                        data.UntranslatedString = multiLine.ToString();
                                                        i = j-1;
                                                    }
                                                } else if(line.Length >= 9) { // Must be at least msgid "x"
                                                    Match matchPluralUntranslated = rgxUntranslatedPlural.Match(line);
                                                    if(matchPluralUntranslated.Success) {
                                                        data.UntranslatedPlural = matchPluralUntranslated.Groups[1].Value;
                                                    } else {
                                                        data.UntranslatedString = matchUntranslated.Groups[1].Value;
                                                    }
                                                }
                                            } else if(line.StartsWith("msgstr") && line.Length >= 9) {
                                                Match matchTranslated = rgxTranslatedString.Match(line);
                                                if(matchTranslated.Success && matchTranslated.Groups[1].Length == 0) { // line is msgstr "" -  multiline string, move to the next line to start reading
                                                    StringBuilder multiLine = new StringBuilder();
                                                    for(j = i + 1; j < lines.Length; j++) {
                                                        string nextLine = lines[j].Trim();
                                                        if(nextLine.Length < 2 || nextLine[0] != '"') { // either an unexpected char or anything but a quote means the end of the multi-line string
                                                            break;
                                                        } else if(line.Length > 3) { // A line could contain just "", but it should be skipped if so
                                                            multiLine.Append(nextLine.Substring(1, nextLine.Length - 2));
                                                        }
                                                    }
                                                    if(multiLine.Length > 0) {
                                                        data.TranslatedString = multiLine.ToString();
                                                        i = j - 1;
                                                    }
                                                } else if(line.Length >= 10) { // Must be at least msgstr "x"
                                                    Match matchTranslatedPlural = rgxTranslatedPlural.Match(line);
                                                    if(matchTranslatedPlural.Success) {
                                                        if(data.TranslatedPlurals == null) {
                                                            data.TranslatedPlurals = new Dictionary<int, Dictionary<string, string>>();
                                                        }
                                                        data.TranslatedPlurals.Add(int.Parse(matchTranslatedPlural.Groups[1].Value), new Dictionary<string, string>() { { language, matchTranslatedPlural.Groups[2].Value } });
                                                    } else {
                                                        data.TranslatedString = matchTranslated.Groups[1].Value;
                                                    }
                                                }
                                            } else if(line.StartsWith("msgctxt") && line.Length >= 10) {
                                                Match matchContext = rgxContext.Match(line);
                                                data.Context = matchContext.Groups[1].Value;
                                            }
                                        }
                                        break;
                                }
                            }
                        }
                        if(translatorComments != null) {
                            data.TranslatorsComments.Add(language, translatorComments.ToString());
                        }
                        string msgIdHash = Utils.MD5(data.UntranslatedString);
                        if(data.TranslatedPlurals != null) {
                            data.TranslatedString = data.TranslatedPlurals[0][language].ToString(); // the singular form
                        }
                        if(!bundles[bundleName].ContainsKey(msgIdHash)) { // first entry in  this bundle
                            bundles[bundleName].Add(msgIdHash, new Dictionary<string, string>());
                            elementData[bundleName].Add(msgIdHash, data);
                        } else { // the meta data has already been added, append the plural and comment meta data from this entry to the existing data
                            if(data.TranslatedPlurals != null) {
                                foreach(KeyValuePair<int, Dictionary<string, string>> plural in data.TranslatedPlurals) {
                                    if(!elementData[bundleName][msgIdHash].TranslatedPlurals.ContainsKey(plural.Key)) {
                                        elementData[bundleName][msgIdHash].TranslatedPlurals.Add(plural.Key, new Dictionary<string, string>());
                                    }
                                    elementData[bundleName][msgIdHash].TranslatedPlurals[plural.Key].Add(language, plural.Value[language]);
                                }
                            }
                            if(data.TranslatorsComments.Count > 0) {
                                foreach(KeyValuePair<string, string> comment in data.TranslatorsComments) {
                                    if(!elementData[bundleName][msgIdHash].TranslatorsComments.ContainsKey(comment.Key)) { // no translator comments in this language
                                        elementData[bundleName][msgIdHash].TranslatorsComments.Add(comment.Key, comment.Value);
                                    } else {
                                        elementData[bundleName][msgIdHash].TranslatorsComments[comment.Key] = comment.Value;
                                    }
                                }
                            }
                        }
                        if(data.TranslatedString.Length > 0 && !bundles[bundleName][msgIdHash].ContainsKey(language)) {
                            bundles[bundleName][msgIdHash].Add(language, data.TranslatedString);
                        }
                    } catch(Exception ex) {
                        LogText(string.Format("Failed to parse entry  '{0}': {1}", entry, ex.Message), true);
                    }
                }
            }
        }

        LogText("Finished parsing, starting import", false);

        try {
            if(db == null || !db.Connected) {
                db = new Database();
                db.OpenConnection();
            }
            int numBundles = 0,
                numElements = 0,
                numResources = 0;
            using(Database.Command cmd = db.CreateCommand())
            using(Database.Command cmdInsertBundle = db.CreateCommand())
            using(Database.Command cmdCheckExisting = db.CreateCommand())
            using(Database.Command cmdInsertElement = db.CreateCommand())
            using(Database.Command cmdUpdateElement = db.CreateCommand())
            using(Database.Command cmdFindSimilar = db.CreateCommand())
            using(Database.Command cmdInsertResource = db.CreateCommand())
            using(Database.Command cmdMaxRow = db.CreateCommand()) {
                string appId = (viwApp.FindControl("radApplication") as RadioButtonList).SelectedValue,
                       defaultLanguage = string.Empty;
                string[] appLanguages = null;
                Hashtable appBundles = new Hashtable(); // <bundlename, bundleid>
                cmd.CommandText = "SELECT defaultlanguage, languages FROM " + db.TablePrefix + "applications WHERE id = @id";
                cmd.AddParameterWithValue("@id", appId);
                DbDataReader reader = cmd.ExecuteReader();
                while(reader.Read()) {
                    defaultLanguage = reader["defaultlanguage"].ToString();
                    appLanguages = reader["languages"].ToString().Trim().Split(',');
                }
                reader.Close();
                cmd.CommandText = "SELECT name, id FROM " + db.TablePrefix + "bundles WHERE application = @id";
                reader = cmd.ExecuteReader();
                while(reader.Read()) {
                    appBundles.Add(reader[0], reader[1]);
                }
                reader.Close();

                cmdInsertBundle.CommandText = "INSERT INTO " + db.TablePrefix + "bundles (id, name, application) VALUES (@id, @name, @application)";
                cmdInsertBundle.AddParameterWithValue("@id", "");
                cmdInsertBundle.AddParameterWithValue("@name", "");
                cmdInsertBundle.AddParameterWithValue("@application", appId);

                cmdCheckExisting.CommandText = "SELECT resourceid FROM " + db.TablePrefix + "elements WHERE applicationid = @appId AND bundle = @bundle AND elementname = @element";
                cmdCheckExisting.AddParameterWithValue("@appId", appId);
                cmdCheckExisting.AddParameterWithValue("@bundle", "");
                cmdCheckExisting.AddParameterWithValue("@element", "");

                cmdInsertElement.CommandText = "INSERT INTO " + db.TablePrefix + "elements (applicationid, bundle, elementname, resourceid, comment) VALUES (@appId, @bundle, @element, @tId, @metaData)";
                cmdInsertElement.AddParameterWithValue("@appId", appId);
                cmdInsertElement.AddParameterWithValue("@bundle", "");
                cmdInsertElement.AddParameterWithValue("@element", "");
                cmdInsertElement.AddParameterWithValue("@tId", "");
                cmdInsertElement.AddParameterWithValue("@metaData", "");

                cmdUpdateElement.CommandText = "UPDATE " + db.TablePrefix + "elements SET comment = @metaData WHERE applicationid = @appId AND bundle = @bundle AND elementname = @element";
                cmdUpdateElement.AddParameterWithValue("@appId", appId);
                cmdUpdateElement.AddParameterWithValue("@bundle", "");
                cmdUpdateElement.AddParameterWithValue("@element", "");
                cmdUpdateElement.AddParameterWithValue("@metaData", "");

                cmdFindSimilar.CommandText = "SELECT resourceid FROM " + db.TablePrefix + "resources WHERE language = @language AND translation = @translation";
                cmdFindSimilar.AddParameterWithValue("@language", defaultLanguage); // Only match if the default language for this app. is present in the uploaded resources
                cmdFindSimilar.AddParameterWithValue("@translation", "");

                switch(db.InstanceType) {
                    case Database.DatabaseType.MySQL:
                        cmdInsertResource.CommandText = "INSERT INTO " + db.TablePrefix + "resources (resourceid, language, translation, translatorcomment) VALUES (@tId, @language, @translation, @comment) ON DUPLICATE KEY UPDATE translation = @translation, translatorcomment = @comment";
                        break;
                    case Database.DatabaseType.SQLServer:
                        cmdInsertResource.CommandText = "UPDATE " + db.TablePrefix + "resources SET translation = @translation, translatorcomment = @comment WHERE resourceid = @tId AND language = @language; IF @@ROWCOUNT = 0 INSERT INTO " + db.TablePrefix + "resources (resourceid, language, translation, translatorcomment) VALUES (@tId, @language, @translation, @comment)";
                        break;
                }
                cmdInsertResource.AddParameterWithValue("@tID", "");
                cmdInsertResource.AddParameterWithValue("@language", "");
                cmdInsertResource.AddParameterWithValue("@translation", "");
                cmdInsertResource.AddParameterWithValue("@comment", "");

                cmdMaxRow.CommandText = "SELECT MAX(resourceid) FROM " + db.TablePrefix + "resources";

                int resourceId = 0;
                string bundleId;
                foreach(KeyValuePair<string, Dictionary<string, Dictionary<string, string>>> bundle in bundles) { // <bundle, <msgId, <language, translation>>>
                    LogText(string.Format("Importing bundle <strong>{0}</strong>", bundle.Key), false);
                    bundleName = bundle.Key;
                    if(appBundles.ContainsKey(bundleName)) {
                        bundleId = appBundles[bundleName].ToString();
                    } else {
                        bundleId = Utils.GenID(true);
                        cmdInsertBundle.Parameters["@id"].Value = bundleId;
                        cmdInsertBundle.Parameters["@name"].Value = bundle.Key;
                        cmdInsertBundle.ExecuteNonQuery();
                        appBundles.Add(bundleName, bundleId);
                    }
                    cmdCheckExisting.Parameters["@bundle"].Value = bundleId;
                    cmdInsertElement.Parameters["@bundle"].Value = bundleId;
                    string elementName; // the first reference for the entry
                    POMetaData thisElementData;
                    foreach(KeyValuePair<string, Dictionary<string, string>> entry in bundle.Value) { // <msgId, <language, translation>>
                        numElements++;
                        thisElementData = elementData[bundleName][entry.Key];
                        string elementContext = thisElementData.Context == null ? "" : "-" + thisElementData.Context;
                        string msgIdSnippet;
                        if(thisElementData.UntranslatedString == "") {
                            elementName = "Generator";
                        } else {
                            if(thisElementData.UntranslatedString.Length > 0) {
                                msgIdSnippet = thisElementData.UntranslatedString.Substring(0, thisElementData.UntranslatedString.Length > 10 ? 10 : thisElementData.UntranslatedString.Length); // truncate to 10 chars
                            } else if(thisElementData.TranslatedString.Length > 0) {
                                msgIdSnippet = thisElementData.TranslatedString.Substring(0, thisElementData.TranslatedString.Length > 10 ? 10 : thisElementData.TranslatedString.Length); // truncate to 10 chars
                            } else {
                                msgIdSnippet = "Unnamed " + entry.Key.Substring(0, 10);
                            }
                            if(thisElementData.References.Count > 0) {
                                string elementReference = elementData[bundleName][entry.Key].References[0]; // use the first reference to generate part of the element name
                                int slashPos = elementReference.LastIndexOf('/'),
                                    colonPos = elementReference.LastIndexOf(':');
                                if(slashPos >= 0) { // directory included
                                    if(colonPos > 1) {
                                        elementName = (elementReference.Substring(slashPos + 1, colonPos - slashPos - 1) + thisElementData.Context + "-" + msgIdSnippet).Replace(' ', '_');
                                    } else {
                                        elementName = (elementReference.Substring(slashPos + 1) + thisElementData.Context + "-" + msgIdSnippet).Replace(' ', '_');
                                    }
                                } else { // no directory
                                    if(colonPos > 1) {
                                        elementName = (elementReference.Substring(0, colonPos) + thisElementData.Context + "-" + msgIdSnippet).Replace(' ', '_');
                                    } else {
                                        elementName = (elementReference + thisElementData.Context + "-" + msgIdSnippet).Replace(' ', '_');
                                    }
                                }
                            } else {
                                elementName = (thisElementData.Context + "-" + msgIdSnippet).Replace(' ', '_');
                            }
                        }
                        cmdCheckExisting.Parameters["@element"].Value = elementName;
                        reader = cmdCheckExisting.ExecuteReader();
                        if(reader.HasRows) { // the element already exists, overwrite existing languages with the ones from the bundle
                            reader.Read();
                            int.TryParse(reader[0].ToString(), out resourceId);
                            reader.Close();
                            cmdUpdateElement.Parameters["@metaData"].Value = elementData[bundleName][entry.Key].ToCommentsField();
                        } else { // no matching elements, create one first
                            reader.Close();
                            cmdInsertElement.Parameters["@element"].Value = elementName;
                            cmdInsertElement.Parameters["@metaData"].Value = elementData[bundleName][entry.Key].ToCommentsField();
                            // check for other resources which might be have the same translation, then append the translations in this bundle to them instead of creating a duplicate resource. 
                            bool readId = false;
                            if(entry.Value.ContainsKey(defaultLanguage)) {
                                cmdFindSimilar.Parameters["@translation"].Value = entry.Value[defaultLanguage];
                                reader = cmdFindSimilar.ExecuteReader();
                                if(reader.HasRows) { // translation of current element matches one already in the database, use the existing resource ID
                                    reader.Read();
                                    resourceId = reader.GetInt32(0);
                                    readId = true;
                                }
                                reader.Close();
                            }
                            if(!readId) { // no matching resources, find the next available resource ID
                                using(DbDataReader maxIdReader = cmdMaxRow.ExecuteReader(CommandBehavior.SingleRow)) {
                                    if(maxIdReader.HasRows) {
                                        maxIdReader.Read();
                                        int.TryParse(maxIdReader[0].ToString(), out resourceId);
                                        resourceId++;
                                    }
                                    maxIdReader.Close();
                                }
                            }
                            cmdInsertElement.Parameters["@tId"].Value = resourceId;
                            cmdInsertElement.ExecuteNonQuery();
                        }

                        cmdInsertResource.Parameters["@tId"].Value = resourceId;
                        foreach(KeyValuePair<string, string> resource in entry.Value) { // <language, translation>>
                            cmdInsertResource.Parameters["@language"].Value = resource.Key;
                            cmdInsertResource.Parameters["@translation"].Value = resource.Value;
                            if(thisElementData.TranslatorsComments.ContainsKey(resource.Key)) {
                                cmdInsertResource.Parameters["@comment"].Value = thisElementData.TranslatorsComments[resource.Key];
                            } else {
                                cmdInsertResource.Parameters["@comment"].Value = DBNull.Value;
                            }
                            try {
                                cmdInsertResource.ExecuteNonQuery();
                                numResources++;
                            } catch(Exception ex) {
                                LogText(ex.Message, true);
                            }
                        }
                        if(!entry.Value.ContainsKey(defaultLanguage) && thisElementData.UntranslatedString.Length > 0) { // the default language hasn't been added, use the untranslated string (msgid) from the PO file
                            cmdInsertResource.Parameters["@language"].Value = defaultLanguage;
                            cmdInsertResource.Parameters["@translation"].Value = thisElementData.UntranslatedString;
                            try {
                                cmdInsertResource.ExecuteNonQuery();
                                numResources++;
                            } catch(Exception ex) {
                                LogText(ex.Message, true);
                            }
                        }

                        if(thisElementData.TranslatedPlurals != null) {
                            foreach(KeyValuePair<int, Dictionary<string,string>> plural in thisElementData.TranslatedPlurals) {
                                if(plural.Key == 0) {
                                    continue;
                                }
                                string pluralName = elementName + "_" + plural.Key;
                                cmdCheckExisting.Parameters["@element"].Value = pluralName;
                                cmdInsertElement.Parameters["@element"].Value = pluralName;
                                reader = cmdCheckExisting.ExecuteReader();
                                if(reader.HasRows) { // the element already exists, overwrite existing languages with the ones from the bundle
                                    reader.Read();
                                    int.TryParse(reader[0].ToString(), out resourceId);
                                }// can't check for similar because the PO format only supports one untranslated plural, so translated plural 2 would overwrite it
                                reader.Close();
                                using(DbDataReader maxIdReader = cmdMaxRow.ExecuteReader(CommandBehavior.SingleRow)) {
                                    if(maxIdReader.HasRows) {
                                        maxIdReader.Read();
                                        int.TryParse(maxIdReader[0].ToString(), out resourceId);
                                        resourceId++;
                                    }
                                    maxIdReader.Close();
                                }
                                cmdInsertResource.Parameters["@tId"].Value = resourceId;
                                cmdInsertElement.Parameters["@tId"].Value = resourceId;
                                cmdInsertElement.ExecuteNonQuery();

                                if(!plural.Value.ContainsKey(defaultLanguage)) {
                                    cmdInsertResource.Parameters["@language"].Value = defaultLanguage;
                                    cmdInsertResource.Parameters["@translation"].Value = thisElementData.UntranslatedPlural;
                                    try {
                                        cmdInsertResource.ExecuteNonQuery();
                                        numResources++;
                                    } catch(Exception ex) {
                                        LogText(ex.Message, true);
                                    }
                                }

                                foreach(KeyValuePair<string, string> translatedPlural in plural.Value) { // <language, plural>>
                                    cmdInsertResource.Parameters["@language"].Value = translatedPlural.Key;
                                    cmdInsertResource.Parameters["@translation"].Value = translatedPlural.Value;
                                    try {
                                        cmdInsertResource.ExecuteNonQuery();
                                        numResources++;
                                    } catch(Exception ex) {
                                        LogText(ex.Message, true);
                                    }
                                }
                            }
                        }
                    }
                    numBundles++;
                }
            }
            LogText(string.Format("Imported {0} resources from {1} bundles containing {2} elements.", numResources, numBundles, numElements), false);
        } catch(Exception ex) {
            LogText(ex.Message, true);
        }
    }

    /// <summary>
    /// Logs text to the progress BulletedList.
    /// </summary>
    /// <param name="text">The text to log.</param>
    /// <param name="error"><c>true</c> if the 'error' CSS class should be applied to the text.</param>
    private void LogText(string text, bool error) {
        List<ListItem> log = Session["log"] as List<ListItem>;
        ListItem itm = new ListItem(text);
        if(error) {
            itm.Attributes.Add("class", "error");
        }
        log.Add(itm);
    }

    /// <summary>
    /// Deserializes the import log from the Session cache and re-populates the progress BulletedList.
    /// Detects completion of the operation and stops the timer.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void tmrImport_Tick(object sender, EventArgs e) {
        try {
            List<ListItem> log = Session["log"] as List<ListItem>;
            lstProgress.Items.Clear();
            lstProgress.Items.AddRange(log.ToArray());
            if(bool.Parse(Session["importDone"].ToString())) {
                (sender as System.Web.UI.Timer).Enabled = false;
                Session.Remove("importFile");
                Session.Remove("importDone");
                imgProgress.Visible = false;
            }
        } catch {
            lstProgress.Items.Add("Error communicating with server, aborting.");
            Session.Remove("importFile");
            Session["importDone"] = bool.TrueString;
        }
    }
}
